---
title: "Python连接SQLServer"
date: 2017-7-14
categories:
- python
tags:
---

<div id="content">
<div id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#orgf14fd7b">安装（centos）</a></li>
<li><a href="#org0197643">使用pyodbc</a></li>
<li><a href="#org5b4c96f">常用函数</a>
<ul>
<li><a href="#orgf15524d">Getting List</a></li>
<li><a href="#orgcd9c0ac">SingleOrDefault</a></li>
</ul>
</li>
</ul>
</div>
</div>
<p>
使用pyodbc连接到SQLServer。pyodbc就是python odbc驱动。
</p>
<p>
还有另外一种叫做 'pymssql' 的驱动，<a href="https://docs.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server">details</a>
</p>
<div class="outline-2" id="outline-container-orgf14fd7b">
<h2 id="orgf14fd7b">安装（centos）</h2>
<div class="outline-text-2" id="text-orgf14fd7b">
<ol class="org-ol">
<li>安装ODBC驱动和SQLServer命令行工具</li>
</ol>
<div class="org-src-container">
<pre class="src src-sh">curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum.repos.d/mssql-tools.repo
sudo <span style="font-weight: bold; font-style: italic;">ACCEPT_EULA</span>=Y yum install msodbcsql mssql-tools
sudo yum install unixODBC-devel
<span style="font-weight: bold;">echo</span> <span style="font-style: italic;">'export PATH="$PATH:/opt/mssql-tools/bin"'</span> &gt;&gt; ~/.bash_profile
<span style="font-weight: bold;">echo</span> <span style="font-style: italic;">'export PATH="$PATH:/opt/mssql-tools/bin"'</span> &gt;&gt; ~/.bashrc
<span style="font-weight: bold;">source</span> ~/.bashrc
</pre>
</div>
<p>
安装后使用命令行工具测试下连接： {% raw %} sqlcmd -S localhost -U sa -P yourpassword to connecto sqlserver {% endraw %}
</p>
<ol class="org-ol">
<li>安装pyodbc</li>
</ol>
<div class="org-src-container">
<pre class="src src-sh">pip install pyodbc
</pre>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-org0197643">
<h2 id="org0197643">使用pyodbc</h2>
<div class="outline-text-2" id="text-org0197643">
<div class="org-src-container">
<pre class="src src-python"><span style="font-weight: bold;">import</span> pyodbc
<span style="font-weight: bold; font-style: italic;">server</span> = <span style="font-style: italic;">'localhost'</span>
<span style="font-weight: bold; font-style: italic;">database</span> = <span style="font-style: italic;">'SampleDB'</span>
<span style="font-weight: bold; font-style: italic;">username</span> = <span style="font-style: italic;">'sa'</span>
<span style="font-weight: bold; font-style: italic;">password</span> = <span style="font-style: italic;">'your_password'</span>
<span style="font-weight: bold; font-style: italic;">cnxn</span> = pyodbc.connect(<span style="font-style: italic;">'DRIVER={ODBC Driver 13 for SQL Server};SERVER='</span>+server+<span style="font-style: italic;">';PORT=1443;DATABASE='</span>+database+<span style="font-style: italic;">';UID='</span>+username+<span style="font-style: italic;">';PWD='</span>+ password)
<span style="font-weight: bold; font-style: italic;">cursor</span> = cnxn.cursor()

<span style="font-weight: bold; font-style: italic;">#</span><span style="font-weight: bold; font-style: italic;">Insert Query</span>
<span style="font-weight: bold;">print</span> (<span style="font-style: italic;">'Inserting a new row into table'</span>)
<span style="font-weight: bold; font-style: italic;">tsql</span> = <span style="font-style: italic;">"INSERT INTO Employees (Name, Location) VALUES (?,?);"</span>
<span style="font-weight: bold;">with</span> cursor.execute(tsql,<span style="font-style: italic;">'Jake'</span>,<span style="font-style: italic;">'United States'</span>):
        <span style="font-weight: bold;">print</span> (<span style="font-style: italic;">'Successfuly Inserted!'</span>)

<span style="font-weight: bold; font-style: italic;">#</span><span style="font-weight: bold; font-style: italic;">Update Query</span>
<span style="font-weight: bold;">print</span> (<span style="font-style: italic;">'Updating Location for Nikita'</span>)
<span style="font-weight: bold; font-style: italic;">tsql</span> = <span style="font-style: italic;">"UPDATE Employees SET Location = ? WHERE Name = ?"</span>
<span style="font-weight: bold;">with</span> cursor.execute(tsql,<span style="font-style: italic;">'Sweden'</span>,<span style="font-style: italic;">'Nikita'</span>):
        <span style="font-weight: bold;">print</span> (<span style="font-style: italic;">'Successfuly Updated!'</span>)


<span style="font-weight: bold; font-style: italic;">#</span><span style="font-weight: bold; font-style: italic;">Delete Query</span>
<span style="font-weight: bold;">print</span> (<span style="font-style: italic;">'Deleting user Jared'</span>)
<span style="font-weight: bold; font-style: italic;">tsql</span> = <span style="font-style: italic;">"DELETE FROM Employees WHERE Name = ?"</span>
<span style="font-weight: bold;">with</span> cursor.execute(tsql,<span style="font-style: italic;">'Jared'</span>):
        <span style="font-weight: bold;">print</span> (<span style="font-style: italic;">'Successfuly Deleted!'</span>)

<span style="font-weight: bold; font-style: italic;">#</span><span style="font-weight: bold; font-style: italic;">Select Query</span>
<span style="font-weight: bold;">print</span> (<span style="font-style: italic;">'Reading data from table'</span>)
<span style="font-weight: bold; font-style: italic;">tsql</span> = <span style="font-style: italic;">"SELECT Name, Location FROM Employees;"</span>
<span style="font-weight: bold;">with</span> cursor.execute(tsql):
        <span style="font-weight: bold; font-style: italic;">row</span> = cursor.fetchone()
        <span style="font-weight: bold;">while</span> row:
                <span style="font-weight: bold;">print</span> (<span style="font-weight: bold;">str</span>(row[0]) + <span style="font-style: italic;">" "</span> + <span style="font-weight: bold;">str</span>(row[1]))
        <span style="font-weight: bold; font-style: italic;"># </span><span style="font-weight: bold; font-style: italic;">row.Name is equals to row[0]</span>
                <span style="font-weight: bold; font-style: italic;">row</span> = cursor.fetchone()
</pre>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-org5b4c96f">
<h2 id="org5b4c96f">常用函数</h2>
<div class="outline-text-2" id="text-org5b4c96f">
</div>
<div class="outline-3" id="outline-container-orgf15524d">
<h3 id="orgf15524d">Getting List</h3>
<div class="outline-text-3" id="text-orgf15524d">
<div class="org-src-container">
<pre class="src src-python"><span style="font-weight: bold; font-style: italic;"># </span><span style="font-weight: bold; font-style: italic;">获取列表，返回字典数组</span>
<span style="font-weight: bold;">def</span> <span style="font-weight: bold;">getlist</span>(conn, sql, *sqlparam):
    <span style="font-weight: bold;">with</span> conn.cursor() <span style="font-weight: bold;">as</span> cursor:
        cursor.execute(sql, sqlparam)
        <span style="font-weight: bold; font-style: italic;">columns</span> = [column[0] <span style="font-weight: bold;">for</span> column <span style="font-weight: bold;">in</span> cursor.description]
        <span style="font-weight: bold; font-style: italic;">retn</span> = []
        <span style="font-weight: bold;">for</span> row <span style="font-weight: bold;">in</span> cursor.fetchall():
            retn.append(<span style="font-weight: bold;">dict</span>(<span style="font-weight: bold;">zip</span>(columns, row)))
    <span style="font-weight: bold;">return</span> retn
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-orgcd9c0ac">
<h3 id="orgcd9c0ac">SingleOrDefault</h3>
<div class="outline-text-3" id="text-orgcd9c0ac">
<div class="org-src-container">
<pre class="src src-python"><span style="font-weight: bold; font-style: italic;"># </span><span style="font-weight: bold; font-style: italic;">获取单条记录，如果结果有多条记录则返回空。返回字典</span>
<span style="font-weight: bold;">def</span> <span style="font-weight: bold;">single_or_default</span>(conn, sql, *sqlparam):
    <span style="font-weight: bold;">with</span> conn.cursor() <span style="font-weight: bold;">as</span> cursor:
        cursor.execute(sql, sqlparam)
        <span style="font-weight: bold;">if</span> cursor.rowcount &gt; 1:
            <span style="font-weight: bold;">return</span> <span style="font-weight: bold; text-decoration: underline;">None</span>
        <span style="font-weight: bold; font-style: italic;">columns</span> = [column[0] <span style="font-weight: bold;">for</span> column <span style="font-weight: bold;">in</span> cursor.description]
        <span style="font-weight: bold; font-style: italic;">row</span> = cursor.fetchone()
        <span style="font-weight: bold;">return</span> <span style="font-weight: bold;">dict</span>(<span style="font-weight: bold;">zip</span>(columns, row))
</pre>
</div>
</div>
</div>
</div>
</div>
<div class="status" id="postamble">
<p class="date">Date: 2017-7-14</p>
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer">Validate</a></p>
</div>
